package com.csu.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

import com.csu.bean.Income;
import com.csu.db.DBUtils;

public class IncomeDaoImpl implements IncomeDao{


	public List<Income> getIncomeByPage(int index, int pageSize) {
		List<Income> list = new ArrayList<>();
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM income LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, pageSize);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			while(rs.next()){
				Income income = new Income();
				income.setItem(rs.getInt(1));//取第一个字段的值\
				income.setEarner(rs.getString(2));
				income.setIncomeType(rs.getString(3));
				income.setIncomeDate(rs.getDate(4));
				income.setSource(rs.getString(5));
				income.setAmount(rs.getBigDecimal(6));
				income.setRemarks(rs.getString(7));
				list.add(income);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		return list;
	}
	
	@Override
	public List<Income> getIncomeByType(String incomeType) {
		// TODO Auto-generated method stub
		List<Income> list = new ArrayList<>();
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM income WHERE incomeType=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, incomeType);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			while(rs.next()){
				Income income = new Income();
				income.setItem(rs.getInt(1));//取第一个字段的值\
				income.setEarner(rs.getString(2));
				income.setIncomeType(rs.getString(3));
				income.setIncomeDate(rs.getDate(4));
				income.setSource(rs.getString(5));
				income.setAmount(rs.getBigDecimal(6));
				income.setRemarks(rs.getString(7));
				list.add(income);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		
		return list;
	}

	@Override
	public int getIncomeCount() {
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT COUNT(*) FROM income";
			ps = conn.prepareStatement(sql);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		return 0;
	}
	
	@Override
	public int getIncomeCountByType(String incomeType) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT COUNT(*) FROM income WHERE incomeType=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, incomeType);
			//5、执行
			rs = ps.executeQuery();
			//6、解析查询结果集
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public int addIncome(String earner,String incomeType,Date incomeDate,String source,BigDecimal amount,String remarks) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "INSERT INTO income VALUES(null,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, earner);
			ps.setString(2, incomeType);
			ps.setDate(3,incomeDate);
			ps.setString(4,source);
			ps.setBigDecimal(5,amount);
			ps.setString(6,remarks);
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int delIncomeById(int item) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "DELETE FROM income WHERE item=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, item);//将outPeople的值代替第一个问号
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		
		return 0;
	}

	@Override
	
	public int selectByEarner(String earner) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		ResultSet rs = null;
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "SELECT * FROM income WHERE earner=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, earner);//将outPeople的值代替第一个问号
			//5、执行
		   rs = ps.executeQuery();
		   if(rs.next()){
				return rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int updateIncome(int item, String earner,String incomeType,Date incomeDate,String source,BigDecimal amount,String remarks) {
		// TODO Auto-generated method stub
		Connection conn = null;//接连对象
		PreparedStatement ps = null;//sql语句的预编译对象
		
		conn = DBUtils.getConnection();
		//4、创建PreparedStatement对象
		try {
			String sql = "UPDATE income SET earner=?,incometype=?,incomedate=?,source=?,amount=?,remarks=?  WHERE item=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, String.valueOf(earner));
			ps.setString(2, String.valueOf(incomeType));
			ps.setDate(3, Date.valueOf(String.valueOf(incomeDate)));
			ps.setString(4, String.valueOf(source));
			ps.setBigDecimal(5, new BigDecimal(String.valueOf(amount)));
			ps.setString(6, String.valueOf(remarks));
			ps.setInt(7, Integer.parseInt("" + item));
			//5、执行
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭资源
			DBUtils.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int selectByIncomeDate(String outDate) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int selectByIncomeType(String outType) {
		// TODO Auto-generated method stub
		return 0;
	}

	

}
